package database.dbRelations;

import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import objects.Course;
import objects.Teacher;

import main.Main;

import database.dbObjects.IDBObject;
import database.dbObjects.IDBCourse.DBCourse;
import database.dbObjects.IDBTeacher.DBTeacher;

public interface IDBRelCourseTeacher extends IDBObject {
	
	public class DBRelCourseTeacher extends DBObject {
		
		public final void init() throws Exception {
			
			createTable();
		}
		
		private final void createTable() throws Exception {
			
			String query = "create table RelCourseTeacher( " +
	        			   "Course_Name char(" + DBCourse.maxLengthName + ") not null, " +
	        			   "Teacher_Email char(" + DBTeacher.maxLengthEmail + ") not null, " +
	        			   "foreign key (Course_Name) references Course (Name), " +
	        			   "foreign key (Teacher_Email) references Teacher (Email), " + 
	        	// WRM contact en niet teacher???
	        			   "primary key (Course_Name, Teacher_Email) " +
	        			       "on conflict abort);";
	        
			executeCreation(query);
		}
		
		public final boolean add(String courseName, String teachEmail) throws Exception {
			
			checkConsistance(courseName, teachEmail);
			
			Vector<Object> v = new Vector<Object>(2);
			v.add(0, courseName);
			v.add(1, teachEmail);
			
			return super.add("RelCourseTeacher", v);
		}
		
		public final boolean delete(String courseName, String teachEmail) throws Exception {
            
            checkConsistance(courseName, teachEmail);
            
            Vector<Object> keys = new Vector<Object>(2);
            keys.add(0, courseName);
            keys.add(1, teachEmail);
            
            return super.delete("RelCourseTeacher", keyColumns, keys);
		}
		
	/*	public void printRelCourseTeacher(String sql) {
			// TODO nog aan te passen!!
			try {
				Statement stat = Main.connection.createStatement();
				ResultSet rs = stat.executeQuery(sql);
				
				System.out.println("Vak ID\t\t|\tLesgever ID");
				System.out.println("---------------------------------------------------------------");
				while (rs.next()) {
					System.out.println(rs.getString("Course_Name")+"\t\t|\t"+rs.getString("Teacher_Email"));
				}
				rs.close();
			} catch(Exception e){
				System.out.println("test-Exception P11!!!");
			}	
		}
	*/	
		
		public final Vector<Course> getCourses(String teachEmail) throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from RelCourseTeacher join Course " +
					"where ((Teacher_Email = '" + teachEmail + "') and " +
					"(RelCourseTeacher.Course_Name = Course.Name));");
			
			Vector<Course> courses = new Vector<Course>();
			int counter = 0;
			while (rs.next()) {
				courses.add(counter, new Course(rs.getString("Name"), 
						rs.getString("Description"), rs.getInt("BeginYear"), 
						rs.getInt("Semester"), rs.getString("MoreInformation")));
				counter++;
			}
			rs.close();
			return courses;
		}
		
		public final Vector<Teacher> getTeachers(String courseName) throws Exception {
			
			Statement stat = Main.connection.createStatement();
			ResultSet rs = stat.executeQuery("select * from RelCourseTeacher join " +
					"(select * from Teacher natural join Contact) " + 
					"where ((Course_Name = '" + courseName + "') and " + 
					"(RelCourseTeacher.Teacher_Email = Email));");
			
			Vector<Teacher> teachers = new Vector<Teacher>();
			int counter = 0;
			while (rs.next()) {
				teachers.add(counter, new Teacher(rs.getString("Email"), 
						rs.getString("Name"), rs.getString("Forename"), 
						rs.getString("PhoneNumber"), rs.getString("MoreInformation"), 
						rs.getString("Office"), rs.getString("Title")));
				counter++;
			}
			rs.close();
			return teachers;
		}
		
		private final void checkConsistance(String courseName, String teachEmail) throws Exception {
			
			course.checkName(courseName);
			teach.checkEmail(teachEmail);
		}
		
		
		private static final Vector<String> keyColumns = new Vector<String>(2);
		static {
			keyColumns.add(0, "Course_Name");
			keyColumns.add(1, "Teacher_Email");
		}
		private final DBCourse course = new DBCourse();
		private final DBTeacher teach = new DBTeacher();
	
	}
	
}
